package com.sql;

public class OracleSql {
	/**
	 * 根据所有者取得该所有者下的表
	 * OWNER (所有者)
	 * TABLE_NAME (表名)
	 * TABLE_TYPE (表类型<表、视图、等>)
	 * COMMENTS (注释)
	 */
	public final static String get_tab_comments="select * from all_tab_comments where owner=? and table_name not like 'BIN%'  order by table_name";
	
	/**
	 * 查询所有表的字段信息
	 * COMMENTS(注释)
	 * TABLE_NAME(表名)
	 * COLUMN_NAME(字段名)
	 * DATA_TYPE(字段类型)
	 * DATA_TYPE_MOD(列的数据类型修饰符)
	 * DATA_TYPE_OWNER(列的数据类型的所有者)
	 * DATA_LENGTH(数据长度*2)
	 * DATA_PRECISION(数值长度)
	 * DATA_SCALE(小数点位数)
	 * NULLABLE(是否允许为空)
	 * COLUMN_ID(列ID)
	 * DEFAULT_LENGTH(默认值长度)
	 * DATA_DEFAULT(默认值)
	 * PrimaryKey(主键)
	 */
	public final static StringBuffer get_tab_col=new StringBuffer("select com.comments,\r\n" + 
			"   col.TABLE_NAME,\r\n" + 
			"   col.COLUMN_NAME,\r\n" + 
			"   col.DATA_TYPE,\r\n" + 
			"   decode(col.DATA_TYPE,'NUMBER',col.DATA_PRECISION,'NVARCHAR2',col.DATA_LENGTH/2,col.DATA_LENGTH) DATA_LENGTH,\r\n" + 
			"   col.DATA_SCALE,\r\n" + 
			"   col.NULLABLE,\r\n" + 
			"   col.COLUMN_ID,\r\n" + 
			"   col.DEFAULT_LENGTH,\r\n" + 
			"   col.DATA_DEFAULT,\r\n" +
//			"   case uc.constraint_type when 'P' then 'Y' else 'N' end \"PrimaryKey\"\r\n" +			
			"  '' \"PrimaryKey\"\r\n" + 
			"from user_tab_columns col \r\n" + 
			"left join user_col_comments com on com.column_name=col.COLUMN_NAME and com.table_name=col.table_name\r\n" + 
			//"left join user_cons_columns ucc on ucc.table_name=col.table_name and ucc.column_name=col.column_name and ucc.position ='1'\r\n" + 
			//"left join user_constraints uc on uc.constraint_name = ucc.constraint_name and uc.constraint_type='P'\r\n" + 
			"where col.TABLE_NAME=? \r\n" + 
			"order by col.table_name asc,col.column_id asc");
}
